/**
 ****************************************************************************
 * Copyright (C) Marcelo F. Ochoa. All rights reserved.                      *
 * ------------------------------------------------------------------------- *
 * This software is published under the terms of the Apache Software License *
 * version 1.1, a copy of which has been included  with this distribution in *
 * the LICENSE file.                                                         *
 */

package com.prism.oracle;

import java.io.IOException;
import java.io.InputStream;
import java.io.StringReader;
import java.io.UnsupportedEncodingException;
import java.io.Writer;
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.util.Enumeration;
import java.util.Vector;

import javax.servlet.http.HttpServletRequest;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import oracle.sql.BLOB;
import oracle.sql.CLOB;

import org.apache.log4j.Logger;

import com.prism.ConnInfo;
import com.prism.Content;
import com.prism.DBConnection;
import com.prism.DownloadRequest;
import com.prism.SPProc;
import com.prism.toolkit.JowaArray;
import com.prism.toolkit.JowaImage;

/**
 * This class implements a ConcreteProduct class of Abstract Factory patterm.
 * Define a product object (DBConnJava8i represent a connection to this DB)
 * to be created by the corresponding concrete factory. Implements the AbstractProduct Interface (DBConnection).
 * Also, implements a ConcreteClass of TemplateMethod patterm.
 * Implements the primitive operations to carry out subclass-specific steps
 * of the algorithm (DBConnection::getGeneratedStream)
 */
public class DBConnJava8i extends DBConnJava {
    Logger log = Logger.getLogger(DBConnJava8i.class);

    protected java.lang.String debugHost = null;
    protected java.lang.String nlsLanguage = null;
    protected java.lang.String nlsTerritory = null;
    protected int debugPort = -1;
    public DBConnJava8i() {
      super();
    }

    /**
     * Create a concrete DBConnection (DBConnJava). Find extra properties attributes of this connection and return a
     * concrete connection object.
     */
    public DBConnection create(ConnInfo cc) {
        DBConnJava8i con = new DBConnJava8i();
        con.connInfo = cc;
        // value to enable remote debugging on oracle 9i platform and JDeveloper
        if (cc.compat.startsWith("9i")) {
          con.debugHost = properties.getProperty("debugHost",null,"DAD_"+cc.connAlias);
          con.debugPort = properties.getIntProperty("debugPort",-1,"DAD_"+cc.connAlias);
        } else { // Service only available on 9i version, disable it or set compat to 9iplsql or 9ijava
          con.debugHost = null;
          con.debugPort = -1;
        }
        // Case Sensity check is not relevant in Oracle 8i Java support
        con.caseSensity = "lower";
        String nlsSetting = properties.getProperty("nls_lang",null,"DAD_"+cc.connAlias);
        if (nlsSetting!=null) {
          try {
            String langSetting = nlsSetting.substring(0,nlsSetting.indexOf("."));
            con.nlsLanguage   = langSetting.substring(0,langSetting.indexOf("_"));
            con.nlsTerritory  = langSetting.substring(langSetting.indexOf("_")+1);
          } catch (IndexOutOfBoundsException e) {
            log.warn("Incorrect syntax on nls_lang parameter: "+nlsSetting);              
          }
        }
        return con;
    }

    /** Concrete operation of Template Method pattern. Pass CGI Enviroment to the DB */
    public void setCGIVars(HttpServletRequest req, String name, String pass) throws SQLException {
        CallableStatement cs = null;
        if (this.debugHost!=null) {
          try {
            cs = sqlconn.prepareCall("{ call DBMS_DEBUG_JDWP.CONNECT_TCP( '"+this.debugHost+"',"+this.debugPort+") }");
            cs.execute();
            if (log.isDebugEnabled())
              log.debug(".setCGIVars - Starting a remote debugging at "+this.debugHost+":"+this.debugPort+" for DAD: "+this.connInfo.connAlias+" - Done. '");
          } catch (SQLException sqe) {
            log.warn("Warning, can't connect to remote debugging session",sqe);
            if (sqe.getErrorCode() == 1031) {
              log.warn("You need DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE privileges in this session.");
            } else if (sqe.getErrorCode() == 30683) {
              log.warn("Select Project->Project Settings...->Debugger-Remote->Listen for JPDA into JDeveloper");              
              log.warn("Then start a debugger session using "+this.debugHost+":"+this.debugPort);              
            }
          } finally {
            if (cs!=null)
              cs.close();
            cs=null;
          }
        }
        if (this.nlsLanguage!=null) {
          try {
            cs = sqlconn.prepareCall("alter session set NLS_LANGUAGE='"+this.nlsLanguage+"' NLS_TERRITORY='"+this.nlsTerritory+"'");
            cs.execute();
            if (log.isDebugEnabled())
              log.debug(".setCGIVars -  set NLS_LANGUAGE='"+this.nlsLanguage+"' NLS_TERRITORY='"+this.nlsTerritory+"' for DAD: "+this.connInfo.connAlias+" - Done.");
          } catch (SQLException sqe) {
            log.warn("Warning, can't alter session: ",sqe);
          } finally {
            if (cs!=null)
              cs.close();
            cs=null;
          }
        }
        super.setCGIVars(req,name,pass);
    }
    
    /**
     * Concrete operation of Template Method Pattern.
     * set the values of the JDBC execute method and execute it.
     */
    public void executeUpdate(CallableStatement cs, 
                              Enumeration procArgListIterator,
                              SPProc plp,
                              HttpServletRequest req) 
        throws SQLException {
        Vector clobPassed = new Vector();
        int foundcount = 0;
        try {
          while (procArgListIterator.hasMoreElements()) {
            String name_args = (String)procArgListIterator.nextElement();
            String urlArgName = getCallerName(name_args, req.getParameterNames());
            String multi_vals[] = req.getParameterValues(urlArgName);
            String argumentType = plp.get(name_args.toLowerCase());
            if (argumentType == null) {
                log.warn("Argument '" + name_args + "' not in procedure description.");
                throw new SQLException("MANY PROCEDURES MATCH NAME, BUT NONE MATCHES SIGNATURE (parameter name '" +
                    name_args + "')");
            }
            //System.out.println("Adding argument "+name_args+" of type "+argumentType);
            if ("LONG RAW".equalsIgnoreCase(argumentType)) { // ARRAY variable
                if (urlArgName.indexOf(".") > 0) { // must be owa_image.point
                    // handle owa_image.point data type
                    JowaImage img = new JowaImage();
                    String val_x = req.getParameter(urlArgName.substring(0, urlArgName.indexOf(".")) + ".x");
                    String val_y = req.getParameter(urlArgName.substring(0, urlArgName.indexOf(".")) + ".y");
                    // the owa_image.point data type is a array of varchar index by binary integer
                    // Position 1 is args.x value
                    // Position 2 is args.y value
                    img.setX(new String(val_x.getBytes(connInfo.clientCharset)));
                    img.setY(new String(val_y.getBytes(connInfo.clientCharset)));
                    cs.setBytes(++foundcount, img.getBytes());
                    if (log.isDebugEnabled())
                      log.debug(".executeUpdate - '"+name_args+"' of type JowaImage passed with setBytes value="+img);
                } else {
                    JowaArray arr = new JowaArray(multi_vals.length);
                    for (int i = 0; i < multi_vals.length; i++) {
                        String s = new String(multi_vals[i].getBytes(connInfo.clientCharset), connInfo.dbCharset);
                        arr.setValueAt(i, s);
                    } // end for make array variable
                    cs.setBytes(++foundcount, arr.getBytes());
                    if (log.isDebugEnabled())
                      log.debug(".executeUpdate - '"+name_args+"' of type JowaArray passed with setBytes value="+arr);
                }
            } else { // otherwise, must be scalar type or cast to scalar
                String s;
                if (urlArgName.indexOf(".") > 0) {
                    s = new String(req.getParameter(req.getParameter(urlArgName.substring(0, urlArgName.indexOf(".")) +
                        ".x")).getBytes(connInfo.clientCharset), connInfo.dbCharset);
                    if (log.isDebugEnabled())
                      log.debug(".executeUpdate - Casting from owa_image.point to varchar2 '"+name_args+"' value="+s);
                } else if (multi_vals != null) {
                    s = new String(multi_vals[0].getBytes(connInfo.clientCharset));
                    if (log.isDebugEnabled())
                      log.debug(".executeUpdate - Casting from array to varchar2 '"+name_args+"' value="+s);
                } else {
                    s = new String(req.getParameter(urlArgName).getBytes(connInfo.clientCharset));
                }
                if ("CLOB".equalsIgnoreCase(argumentType)) {
                  CLOB tmpClob = CLOB.createTemporary(this.sqlconn,false,CLOB.DURATION_SESSION);
                  try {
                    Writer iow = tmpClob.setCharacterStream(0);
                    iow.write(s.toCharArray());
                    iow.flush();
                    iow.close();
                  } catch (IOException ioe) {
                    throw new SQLException("DBConnJava8i: Failed to write temporary CLOB:\n"+ioe.getMessage());
                  }
                  clobPassed.add(tmpClob);
                  cs.setClob(++foundcount,tmpClob);
                  if (log.isDebugEnabled())
                    log.debug(".executeUpdate - '"+name_args+"' passed with setClob value='"+s+"'");
                } else {
                  cs.setString(++foundcount, s);
                  if (log.isDebugEnabled())
                    log.debug(".executeUpdate - '"+name_args+"' passed with setString value='"+s+"'");
                }
            } // end scalar type or cast to scalar
          } // end while has arguments      
          // Exec procedure in DB
          cs.executeUpdate();
        } catch (UnsupportedEncodingException ue) {
            if (log.isDebugEnabled())
              log.debug(".executeUpdate - Java 8i/9i Adapter - Error Decoding arguments values",ue);
            throw new SQLException("Java 8i/9i Adapter - Error Decoding arguments values:\n" + ue.getMessage());
        } finally {
          for(int i=0;i<clobPassed.size();i++) {
            CLOB tmpClob = (CLOB)clobPassed.get(i);
            if (tmpClob!= null) {
              CLOB.freeTemporary(tmpClob);
              tmpClob = null;
            }
          }
          clobPassed.clear();
          clobPassed = null;
        }
    }
    
    /**
     * Concrete operation of Template Method pattern. Return generated page as StringReader
     * Uses BLOB because CLOB datatypes has several encoding problems with no ascii chars
     */
    public Content getGeneratedStream(HttpServletRequest req) throws IOException, SQLException, UnsupportedEncodingException {
        DownloadRequest download = null;
        Content generatedContent = new Content();
        BLOB locator = null;
        CallableStatement cs = null;
        InputStream in_stream = null;
        cs = sqlconn.prepareCall("{ call JxtpBuff.flush() }");
        cs.execute();
        cs.close();
        cs = sqlconn.prepareCall("{ ? = call JxtpBuff.getPage() }");
        cs.registerOutParameter(1, OracleTypes.BLOB);
        cs.execute();
        locator = ((OracleCallableStatement)cs).getBLOB(1);
        int i = (int)locator.length();
        in_stream = locator.getBinaryStream();
        byte[] b_array = new byte[i];
        in_stream.read(b_array);
        cs.close();
        cs = null;
        if (log.isDebugEnabled())
          log.debug(".getGeneratedStream - Page=>\n"+new String(b_array,connInfo.dbCharset));
        try {
            cs = sqlconn.prepareCall("{ call JxtpBuff.free(?) }");
            ((OracleCallableStatement)cs).setBLOB(1, locator);
            cs.executeUpdate();
        } catch (SQLException sqe) { // ignore errors, if temporary CLOB already was free
            log.warn(".getGeneratedStream - Error freeing temporary BLOB",sqe);
        } finally {
            cs.close();
            cs = null;
        }
        generatedContent.setPage(new StringReader(new String(b_array, connInfo.dbCharset)));
        try {
            download =
                this.connInfo.getFactory().createDownloadRequest(req,this);
            if (download!=null && download.isFileDownload())
              generatedContent.setInputStream(download.getStream(download.getDownloadInfo()));
        } catch (IOException e) {
            log.warn(".getGeneratedStream -  error getting the InputStrem in a inline download",e);
        }
        return generatedContent;
    }
}
